<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic
  PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<topic id="topic1"><title id="bw20941">CREATE OPERATOR</title><body><p id="sql_command_desc">Defines a new operator.</p><section id="section2"><title>Synopsis</title><codeblock id="sql_command_synopsis">CREATE OPERATOR <varname>name</varname> ( 
       PROCEDURE = <varname>funcname</varname>
       [, LEFTARG = <varname>lefttype</varname>] [, RIGHTARG = <varname>righttype</varname>]
       [, COMMUTATOR = <varname>com_op</varname>] [, NEGATOR = <varname>neg_op</varname>]
       [, RESTRICT = <varname>res_proc</varname>] [, JOIN = <varname>join_proc</varname>]
       [, HASHES] [, MERGES] )</codeblock></section><section id="section3"><title>Description</title><p><codeph>CREATE OPERATOR</codeph> defines a new operator. The user who
defines an operator becomes its owner. </p><p>The operator name is a sequence of up to <codeph>NAMEDATALEN</codeph>-1
(63 by default) characters from the following list: <codeph>+ - * / &lt;
&gt; = ~ ! @ # % ^ &amp; | ` ?</codeph></p><p>There are a few restrictions on your choice of name: </p><ul><li id="bw144996"><codeph>--</codeph> and <codeph>/*</codeph> cannot appear anywhere in
an operator name, since they will be taken as the start of a comment.
</li><li id="bw144998">A multicharacter operator name cannot end in <codeph>+</codeph> or <codeph>-</codeph>,
unless the name also contains at least one of these characters: <codeph>~
! @ # % ^ &amp; | ` ?</codeph></li></ul><p>For example, <codeph>@-</codeph> is an allowed operator name, but <codeph>*-</codeph>
is not. This restriction allows Greenplum Database to parse SQL-compliant
commands without requiring spaces between tokens. </p>
<p>The use of <codeph>=&gt;</codeph> as an operator name is deprecated.  
It may be disallowed altogether in a future release.</p>
<p>The operator <codeph>!=</codeph> is mapped to <codeph>&lt;&gt;</codeph>
on input, so these two names are always equivalent. </p><p>At least one of <codeph>LEFTARG</codeph> and <codeph>RIGHTARG</codeph>
must be defined. For binary operators, both must be defined. For right
unary operators, only <codeph>LEFTARG</codeph> should be defined, while
for left unary operators only <codeph>RIGHTARG</codeph> should be defined.
</p><p>The <varname>funcname</varname> procedure must have been previously defined using
<codeph>CREATE FUNCTION</codeph>, must be <codeph>IMMUTABLE</codeph>,
and must be defined to accept the correct number of arguments (either
one or two) of the indicated types. </p><p>The other clauses specify optional operator optimization clauses.
These clauses should be provided whenever appropriate to speed up queries
that use the operator. But if you provide them, you must be sure that
they are correct. Incorrect use of an optimization clause can result
in server process crashes, subtly wrong output, or other unexpected results.
You can always leave out an optimization clause if you are not sure about
it.</p>
            <p>To be able to create an operator, you must have <codeph>USAGE</codeph> privilege on
                the argument types and the return type, as well as <codeph>EXECUTE</codeph>
                privilege on the underlying function. If a commutator or negator operator is
                specified, you must own these operators.</p></section><section id="section4"><title>Parameters</title><parml><plentry><pt><varname>name</varname></pt><pd>The (optionally schema-qualified) name of the operator to be defined.
Two operators in the same schema can have the same name if they operate
on different data types.</pd></plentry><plentry><pt><varname>funcname</varname></pt><pd>The function used to implement this operator (must be an <codeph>IMMUTABLE</codeph>
function).</pd></plentry><plentry><pt><varname>lefttype</varname></pt><pd>The data type of the operator's left operand, if any. This option
would be omitted for a left-unary operator. </pd></plentry><plentry><pt><varname>righttype</varname></pt><pd>The data type of the operator's right operand, if any. This option
would be omitted for a right-unary operator.</pd></plentry><plentry><pt><varname>com_op</varname></pt><pd>The optional <codeph>COMMUTATOR</codeph> clause names an operator
that is the commutator of the operator being defined. We say that operator
A is the commutator of operator B if (x A y) equals (y B x) for all possible
input values x, y. Notice that B is also the commutator of A. For example,
operators <codeph>&lt;</codeph> and <codeph>&gt;</codeph> for a particular
data type are usually each others commutators, and operator + is usually
commutative with itself. But operator <codeph>-</codeph> is usually not
commutative with anything. The left operand type of a commutable operator
is the same as the right operand type of its commutator, and vice versa.
So the name of the commutator operator is all that needs to be provided
in the <codeph>COMMUTATOR</codeph> clause. </pd></plentry><plentry><pt><varname>neg_op</varname></pt><pd>The optional <codeph>NEGATOR</codeph> clause names an operator that
is the negator of the operator being defined. We say that operator A
is the negator of operator B if both return Boolean results and (x A
y) equals NOT (x B y) for all possible inputs x, y. Notice that B is
also the negator of A. For example, <codeph>&lt;</codeph> and <codeph>&gt;=</codeph>
are a negator pair for most data types. An operator's negator must
have the same left and/or right operand types as the operator to be defined,
so only the operator name need be given in the <codeph>NEGATOR</codeph>
clause.</pd></plentry><plentry><pt><varname>res_proc</varname></pt><pd>The optional <codeph>RESTRICT</codeph> names a restriction selectivity
estimation function for the operator. Note that this is a function name,
not an operator name. <codeph>RESTRICT</codeph> clauses only make sense
for binary operators that return <codeph>boolean</codeph>. The idea behind
a restriction selectivity estimator is to guess what fraction of the
rows in a table will satisfy a <codeph>WHERE</codeph>-clause condition
of the form:</pd><pd><codeblock>column OP constant</codeblock></pd><pd>for the current operator and a particular constant value. This assists
the optimizer by giving it some idea of how many rows will be eliminated
by <codeph>WHERE</codeph> clauses that have this form. </pd><pd>You can usually just use one of the following system standard estimator functions for many of
                        your own operators: <p><codeph>eqsel</codeph> for =
                                </p><p><codeph>neqsel</codeph> for &lt;&gt;
                                </p><p><codeph>scalarltsel</codeph> for &lt; or &lt;=
                                </p><p><codeph>scalargtsel</codeph> for &gt; or &gt;= </p></pd></plentry><plentry><pt><varname>join_proc</varname></pt><pd>The optional <codeph>JOIN</codeph> clause names a join selectivity
estimation function for the operator. Note that this is a function name,
not an operator name. <codeph>JOIN</codeph> clauses only make sense for
binary operators that return <codeph>boolean</codeph>. The idea behind
a join selectivity estimator is to guess what fraction of the rows in
a pair of tables will satisfy a <codeph>WHERE</codeph>-clause condition
of the form</pd><pd><codeblock>table1.column1 OP table2.column2</codeblock></pd><pd>for the current operator. This helps the optimizer by letting it figure
out which of several possible join sequences is likely to take the least
work.</pd><pd>You can usually just use one of the following system standard join selectivity estimator
                        functions for many of your own operators:<p><codeph>eqjoinsel</codeph> for =
                                </p><p><codeph>neqjoinsel</codeph> for &lt;&gt;
                                </p><p><codeph>scalarltjoinsel</codeph> for &lt; or &lt;=
                                </p><p><codeph>scalargtjoinsel</codeph> for &gt; or &gt;=
                                </p><p><codeph>areajoinsel</codeph> for 2D area-based comparisons
                                </p><p><codeph>positionjoinsel</codeph> for 2D position-based
                            comparisons </p><p><codeph>contjoinsel</codeph> for 2D containment-based
                            comparisons </p></pd></plentry><plentry><pt>HASHES</pt><pd>The optional <codeph>HASHES</codeph> clause tells the system that it is permissible to use the
                        hash join method for a join based on this operator. <codeph>HASHES</codeph>
                        only makes sense for a binary operator that returns
                        <codeph>boolean</codeph>. The hash join operator can only return true for
                        pairs of left and right values that hash to the same hash code. If two
                        values are put in different hash buckets, the join will never compare them,
                        implicitly assuming that the result of the join operator must be false.
                        Because of this, it never makes sense to specify <codeph>HASHES</codeph> for
                        operators that do not represent equality. </pd>
                    <pd>In most cases, it is only practical to support hashing for operators that
                        take the same data type on both sides. However, you can design compatible
                        hash functions for two or more data types, which are functions that will
                        generate the same hash codes for "equal" values, even if the values are
                        differently represented. </pd><pd>To be marked <codeph>HASHES</codeph>, the join operator must appear in a hash index operator
                        class. Attempts to use the operator in hash joins will fail at run time if
                        no such operator class exists. The system needs the operator class to find
                        the data-type-specific hash function for the operator's input data type. You
                        must also supply a suitable hash function before you can create the operator
                        class. Exercise care when preparing a hash function, as there are
                        machine-dependent ways in which it could fail to function correctly. For
                        example, on machines that meet the IEEE floating-point standard, negative
                        zero and positive zero are different values (different bit patterns) but are
                        defined to compare as equal. If a float value could contain a negative zero,
                        define it to generate the same hash value as positive zero.</pd>
                    <pd>
                        <p>A hash-joinable operator must have a commutator (itself, if the two
                            operand data types are the same, or a related equality operator if they
                            are different) that appears in the same operator family. Otherwise,
                            planner errors can occur when the operator is used. For better
                            optimization, a hash operator family that supports multiple data types
                            should provide equality operators for every combination of the data
                            types. <note>The function underlying a hash-joinable operator must be
                                marked immutable or stable; an operator marked as volatile will not
                                be used. If a hash-joinable operator has an underlying function that
                                is marked strict, the function must also be complete, returning true
                                or false, and not null, for any two non-null inputs.</note></p>
                    </pd></plentry><plentry><pt>MERGES</pt><pd>The <codeph>MERGES</codeph> clause, if present, tells the system
that it is permissible to use the merge-join method for a join based
on this operator. <codeph>MERGES</codeph> only makes sense for a binary
operator that returns <codeph>boolean</codeph>, and in practice the operator
must represent equality for some data type or pair of data types. </pd>
                    <pd>Merge join is based on the idea of sorting the left- and right-hand tables
                        into order and then scanning them in parallel. This means both data types
                        must be capable of being fully ordered, and the join operator must be one
                        that can only succeed for pairs of values that fall at equivalent places in
                        the sort order. In practice, this means that the join operator must behave
                        like an equality operator. However, you can merge-join two distinct data
                        types so long as they are logically compatible. For example, the
                            <codeph>smallint-versus-integer</codeph> equality operator is
                        merge-joinable. Only sorting operators that bring both data types into a
                        logically compatible sequence are needed. </pd>
                    <pd>To be marked <codeph>MERGES</codeph>, the join operator must appear as an
                        equality member of a btree index operator family. This is not enforced when
                        you create the operator, because the referencing operator family does not
                        exist until later. However, the operator will not actually be used for merge
                        joins unless a matching operator family can be found. The
                            <codeph>MERGE</codeph> flag thus acts as a suggestion to the planner to
                        look for a matching operator family.</pd>
                    <pd>A merge-joinable operator must have a commutator that appears in the same
                        operator family. This would be itself, if the two operand data types are the
                        same, or a related equality operator if the data types are different.
                        Without an appropriate commutator, planner errors can occur when the
                        operator is used. Also, although not strictly required, a btree operator
                        family that supports multiple data types should be able to provide equality
                        operators for every combination of the data types; this allows better
                        optimization. <note><codeph>SORT1</codeph>, <codeph>SORT2</codeph>,
                                <codeph>LTCMP</codeph>, and <codeph>GTCMP</codeph> were formerly
                            used to specify the names of sort operators associated with a
                            merge-joinable operator. Information about associated operators is now
                            found by looking at B-tree operator families; specifying any of these
                            operators will be ignored, except that it will implicitly set
                                <codeph>MERGES</codeph> to true. </note></pd></plentry></parml></section><section id="section5"><title>Notes</title><p>Any functions used to implement the operator must be defined as <codeph>IMMUTABLE</codeph>.</p>
                     <p>   It is not possible to specify an operator's lexical precedence in
                           <codeph>CREATE OPERATOR</codeph>, because the parser's precedence behavior
                         is hard-wired. See <xref href="https://www.postgresql.org/docs/9.4/sql-syntax-lexical.html#SQL-PRECEDENCE" 
                             scope="external" format="html">Operator Precedence</xref> in the PostgreSQL 
                         documentation for precedence details.</p>
                      <p>Use <codeph><xref href="DROP_OPERATOR.xml#topic1">DROP
                    OPERATOR</xref></codeph> to delete user-defined operators from a database. Use
                        <codeph><xref href="ALTER_OPERATOR.xml#topic1">ALTER
                    OPERATOR</xref></codeph> to modify operators in a database.</p></section>
    <section id="section6"><title>Examples</title><p>Here is an example of creating an operator for adding two complex numbers,
assuming we have already created the definition of type <codeph>complex</codeph>.
First define the function that does the work, then define the operator:
</p><codeblock>CREATE FUNCTION complex_add(complex, complex)
    RETURNS complex
    AS 'filename', 'complex_add'
    LANGUAGE C IMMUTABLE STRICT;
CREATE OPERATOR + (
    leftarg = complex,
    rightarg = complex,
    procedure = complex_add,
    commutator = +
);</codeblock><p>To use this operator in a query: </p><codeblock>SELECT (a + b) AS c FROM test_complex;</codeblock></section><section id="section7"><title>Compatibility</title><p><codeph>CREATE OPERATOR</codeph> is a Greenplum Database language extension.
The SQL standard does not provide for user-defined operators.</p></section><section id="section8"><title>See Also</title><p><codeph><xref href="CREATE_FUNCTION.xml#topic1" type="topic" format="dita"/></codeph>,
                        <codeph><xref href="./CREATE_TYPE.xml#topic1" type="topic" format="dita"
                    /></codeph>, <codeph><xref href="ALTER_OPERATOR.xml#topic1" type="topic"
                        format="dita"/></codeph>, <codeph><xref href="./DROP_OPERATOR.xml#topic1"
                        type="topic" format="dita"/></codeph></p></section></body></topic>
